﻿using System;
using System.Collections.Generic;
using System.Text;
using DTC.Models;
using Tools.Common;
using System.Data;
using System.Configuration;
using System.Data.SQLite;
namespace DTC.DAL
{
    public class Test_DatasService
    {
        static object objLock = new object();
        public static int CreateDataBase()
        {
                string sql = @"CREATE TABLE IF NOT EXISTS TEST_DATA ( 
                                ID       INTEGER  PRIMARY KEY AUTOINCREMENT,
                                MODEL    TEXT,
                                NAME     TEXT,
                                MIN      TEXT,
                                MAX      TEXT,
                                VALUE1   TEXT,
                                VALUE2   TEXT,
                                RESULT   TEXT,
                                GUID     TEXT,
                                ADD_TIME DATETIME 
                            );";
                try
                {
                    return SQLiteHelper.ExecuteNonQuery(sql, null);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
        }
        public static int CreateTriger_DEL_AF_8()
        {
            string sql = @"CREATE TRIGGER DEL_BF_30
                           AFTER INSERT ON TEST_DATA
                           FOR EACH ROW
                           WHEN time( 'now' ) = time( '12:00:00' ) 
                    BEGIN
                        DELETE
                          FROM test_data
                         WHERE date( add_time ) < date( 'now', '-8 day' );
                    END;";
                try
                {
                    return SQLiteHelper.ExecuteNonQuery(sql, null);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
        }
        public static int auto_vacuum()
        {
            string sql = @"pragma auto_vacuum = INCREMENTAL;";
            try
            {
                return SQLiteHelper.ExecuteNonQuery(sql, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static int Create_Index()
        {
            string sql = @"CREATE INDEX idx_TEST_DATA ON TEST_DATA ( 
                        GUID 
                    );";
            try
            {
                return SQLiteHelper.ExecuteNonQuery(sql, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static int InsertTestData(Test_Data dt)
        {
            #region sql准备
            string SQL = string.Format(@"INSERT INTO TEST_DATA(
                                                MODEL,
                                                NAME,
                                                MIN,
                                                MAX,
                                                VALUE1,
                                                VALUE2,
                                                RESULT,
                                                GUID,
                                                ADD_TIME
                                            )
                                            VALUES(
                                                '{0}',
                                                '{1}',
                                                '{2}',
                                                '{3}',
                                                '{4}',
                                                '{5}',
                                                '{6}',
                                                '{7}',
                                                '{8}'                                       
                                            )"
                                      , dt.Model
                                      , dt.Name
                                      , dt.Min
                                      , dt.Max
                                      , dt.Value1
                                      , dt.Value2
                                      , dt.Result
                                      , dt.GUID
                                      , DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            #endregion
            try
            {
                lock (objLock)
                {
                    return SQLiteHelper.ExecuteNonQuery(SQL, null);
                }             
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        /// 定义事务  DbTransaction trans = conn.BeginTransaction();  
        ///     或者：SQLiteTransaction trans = Conn.BeginTransaction();  
        /// 操作代码示例：  
        /// try  
        ///{  
        ///    // 连续操作记录   
        ///    for (int i = 0; i < 1000; i++)  
        ///    {  
        ///        ExecuteNonQuery(trans,commandText,[] paramList);  
        ///    }  
        ///    trans.Commit();  
        ///}  
        ///catch  
        ///{  
        ///    trans.Rollback();  
        ///    throw;  
        ///}  
        ///trans.Connection.Close();//关闭事务连接  
        ///transaction.Dispose();//释放事务对象  
        ///
         public static int InsertTestDatas(List<Test_Data> dts)
        {

            lock (objLock)
            {
                SQLiteConnection con = new SQLiteConnection(SQLiteHelper.ConnectionString);
                if (con.State == ConnectionState.Closed)
                    con.Open();
                SQLiteTransaction trans = con.BeginTransaction();
                try
                {
                    foreach (var dt in dts)
                    {
                        #region Sql prepare
                        string SQL = string.Format(@"INSERT INTO TEST_DATA(
                                                MODEL,
                                                NAME,
                                                MIN,
                                                MAX,
                                                VALUE1,
                                                VALUE2,
                                                RESULT,
                                                GUID,
                                                ADD_TIME
                                            )
                                            VALUES(
                                                '{0}',
                                                '{1}',
                                                '{2}',
                                                '{3}',
                                                '{4}',
                                                '{5}',
                                                '{6}',
                                                '{7}',
                                                '{8}'                                       
                                            )"
                                     , dt.Model
                                     , dt.Name
                                     , dt.Min
                                     , dt.Max
                                     , dt.Value1
                                     , dt.Value2
                                     , dt.Result
                                     , dt.GUID
                                     , DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                        #endregion
                        SQLiteHelper.ExecuteNonQuery(trans, SQL, null);
                    }
                    trans.Commit();
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
                con.Close();
            }
         //   trans.Dispose();//释放事务对象 
      
        //    trans.Connection.Close();//关闭事务连接  
            return 1;
        }
        public static List<Test_Data> getDatabyName(string Name,string beginDate,string EndDate)
        {
            try
            {
                string SQL = string.Format(@"select * from Test_Data where NAME='{0}' and datetime(add_time) between datetime('{1}') and datetime('{2}')", Name,beginDate,EndDate);
                List<Test_Data> tds = new List<Test_Data>();
                using (DataSet ds = SQLiteHelper.ExecuteDataSet(SQL, null))
                {
                    if (ds.Tables.Count > 0)
                    {
                        
                        DataTable dt = ds.Tables[0];
                        foreach (DataRow dr in dt.Rows)
                        {
                            Test_Data td = new Test_Data();
                            td.ID = int.Parse((dr["ID"]).ToString());
                            td.Max = dr["MAX"].ToString();
                            td.Min = dr["MIN"].ToString();
                            td.Model = dr["MODEL"].ToString();
                            td.Name = dr["NAME"].ToString();
                            td.Result = dr["RESULT"].ToString();
                            td.Value1 = dr["VALUE1"].ToString();
                            td.Value2 = dr["VALUE2"].ToString();
                            td.GUID = dr["GUID"].ToString();
                            td.AddTime = Convert.ToDateTime(dr["ADD_TIME"].ToString());
                            tds.Add(td);
                        }
                    }
                }
                return tds;
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        public static DataTable GetTestDataTable(string BeginTime, string EndTime)
        {
            try
            {
                string[] items = ConfigurationManager.AppSettings["QueryItems"].Split(new char[] { ',' });
                string sql = GenSQLView(items, BeginTime, EndTime);
                return SQLiteHelper.ExecuteDataSet(sql, null).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
        }
        private static string GenSQLView(string[] items,string BeginTime,string EndTime)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from ");
            List<string> sqllist = new List<string>();
            foreach(string item in items)
            {
                string sqltemp = GenSqlTemp(item, BeginTime, EndTime);
                sqllist.Add(sqltemp);
            }
            for (int i = 0; i < sqllist.Count; i++)
            {
                string temp;
                if (i == 0)
                {
                    temp = string.Format(@"({0}) as t0  left join ", sqllist[i]);
                }
                else if(i<sqllist.Count-1)
                    temp = string.Format(@"({0}) as t{1} ON t{2}.guid = t0.guid left join ", sqllist[i], i,i);
                else
                    temp = string.Format(@"({0}) as t{1} ON t{2}.guid = t0.guid", sqllist[i], i,i);
                sb.Append(temp);
            }
            return sb.ToString();
        }
        private static string GenSqlTemp(string item,string benginTime,string endTime)
        {
            string Model = ConfigurationManager.AppSettings["CurrentModel"];
            string sql = string.Format(@"select * from Test_Data where NAME='{0}' and datetime(add_time) between datetime('{1}') and datetime('{2}') and model='{3}'", item, benginTime, endTime, Model);
            return sql;
        }
    }
}
